const fs = require('fs');
const path = require('path');
const { Sequelize, DataTypes } = require('sequelize');

// 数据库配置
const sequelize = new Sequelize({
  dialect: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: '888mei525',
  database: 'lingxi_system',
  logging: false // 关闭SQL日志
});

// 定义销售订单模型
const SalesOrderModel = sequelize.define('sales_order', {
  id: {
    type: DataTypes.BIGINT,
    primaryKey: true,
    autoIncrement: true
  },
  organizationId: {
    type: DataTypes.INTEGER,
    allowNull: false,
    field: 'organization_id'
  },
  platformOrderId: {
    type: DataTypes.STRING(200),
    allowNull: false,
    unique: true,
    field: 'platform_order_id'
  },
  customerId: {
    type: DataTypes.BIGINT,
    allowNull: false,
    field: 'customer_id'
  },
  orderAmount: {
    type: DataTypes.DECIMAL(12, 2),
    allowNull: false,
    field: 'order_amount'
  },
  orderStatus: {
    type: DataTypes.ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled'),
    allowNull: true,
    defaultValue: 'pending',
    field: 'order_status'
  },
  orderDate: {
    type: DataTypes.DATE,
    allowNull: false,
    field: 'order_date'
  },
  platformSource: {
    type: DataTypes.ENUM('zcy', 'manual'),
    allowNull: true,
    defaultValue: 'zcy',
    field: 'platform_source'
  },
  invoiceStatus: {
    type: DataTypes.ENUM('uninvoiced', 'invoiced', 'sent'),
    allowNull: true,
    defaultValue: 'uninvoiced',
    field: 'invoice_status'
  },
  invoiceDate: {
    type: DataTypes.DATE,
    allowNull: true,
    field: 'invoice_date'
  },
  responsibleSalesman: {
    type: DataTypes.STRING(100),
    allowNull: true,
    field: 'responsible_salesman'
  },
  isGift: {
    type: DataTypes.BOOLEAN,
    allowNull: true,
    defaultValue: false,
    field: 'is_gift'
  },
  collectionStatus: {
    type: DataTypes.ENUM('pending', 'contacted', 'promised', 'paid', 'difficult'),
    allowNull: true,
    defaultValue: 'pending',
    field: 'collection_status'
  },
  collectionCount: {
    type: DataTypes.INTEGER,
    allowNull: true,
    defaultValue: 0,
    field: 'collection_count'
  },
  lastCollectionDate: {
    type: DataTypes.DATE,
    allowNull: true,
    field: 'last_collection_date'
  },
  lastCollectionResult: {
    type: DataTypes.STRING(200),
    allowNull: true,
    field: 'last_collection_result'
  },
  nextFollowupDate: {
    type: DataTypes.DATE,
    allowNull: true,
    field: 'next_followup_date'
  },
  orderNo: {
    type: DataTypes.STRING(50),
    allowNull: false,
    unique: true,
    field: 'order_no'
  }
}, {
  tableName: 'sales_orders',
  timestamps: true,
  createdAt: 'created_at',
  updatedAt: 'updated_at'
});

// 定义销售订单项模型
const SalesOrderItemModel = sequelize.define('sales_order_item', {
  id: {
    type: DataTypes.BIGINT,
    primaryKey: true,
    autoIncrement: true
  },
  orderId: {
    type: DataTypes.BIGINT,
    allowNull: false,
    field: 'order_id'
  },
  productId: {
    type: DataTypes.STRING(100),
    allowNull: true,
    field: 'product_id'
  },
  productName: {
    type: DataTypes.STRING(200),
    allowNull: false,
    field: 'product_name'
  },
  specification: {
    type: DataTypes.STRING(200),
    allowNull: true
  },
  quantity: {
    type: DataTypes.INTEGER,
    allowNull: false
  },
  unit: {
    type: DataTypes.STRING(20),
    allowNull: true
  },
  unitPrice: {
    type: DataTypes.DECIMAL(12, 2),
    allowNull: false,
    field: 'unit_price'
  },
  amount: {
    type: DataTypes.DECIMAL(12, 2),
    allowNull: false
  },
  remark: {
    type: DataTypes.STRING(200),
    allowNull: true
  }
}, {
  tableName: 'sales_order_items',
  timestamps: true,
  createdAt: 'created_at',
  updatedAt: 'updated_at'
});

// 模拟 ctx.logger
const logger = {
  info: (...args) => console.log('[INFO]', ...args),
  warn: (...args) => console.log('[WARN]', ...args),
  error: (...args) => console.log('[ERROR]', ...args)
};

// 从插件数据中提取订单的函数（简化版）
function extractOrdersFromPluginData(item) {
  try {
    // 检查数据结构 - 改为检查response.result.data
    if (!item || !item.response || !item.response.result || !item.response.result.data) {
      logger.warn('[PluginData] 插件数据格式不正确，缺少response.result.data');
      return [];
    }
    
    // 获取响应数据 - 改为从response.result.data获取
    let responseData = item.response.result.data;
    if (typeof responseData === 'string') {
      try {
        responseData = JSON.parse(responseData);
      } catch (parseError) {
        logger.warn('[PluginData] 插件数据格式不正确，无法解析response.result.data');
        return [];
      }
    }
    
    // 检查是否为订单数据格式 - 修正嵌套结构
    let orderDataArray = [];
    
    // 处理政采云数据结构: response.result.data是一个订单数组
    if (Array.isArray(responseData) && responseData.length > 0) {
      // 直接使用responseData作为订单数组
      orderDataArray = responseData;
    }
    
    if (orderDataArray.length === 0) {
      logger.warn('[PluginData] 订单数据格式不正确，未找到有效的订单数据数组');
      return [];
    }
    
    // 提取订单数据
    const orders = orderDataArray.map(orderItem => {
      // 获取订单基本信息
      const order = orderItem.order || {};
      
      // 获取订单项
      const items = orderItem.items || [];
      
      return {
        // 订单基本信息
        platformOrderId: order.id,  // 平台订单ID
        customerId: order.purchaserId,  // 客户ID
        orderAmount: parseFloat(order.orderTotalFee) || 0,  // 订单金额
        orderStatus: mapOrderStatus(order.status),  // 订单状态
        orderDate: order.createdAt ? new Date(order.createdAt) : new Date(),  // 订单日期
        platformSource: order.platformSource || 'zcy',  // 平台来源
        invoiceStatus: mapInvoiceStatus(order.invoiceStatus),  // 开票状态
        invoiceDate: order.invoiceDate ? new Date(order.invoiceDate) : null,  // 开票日期
        responsibleSalesman: order.responsibleSalesman || '',  // 负责销售
        isGift: order.isGift === 1,  // 是否含赠品
        collectionStatus: order.collectionStatus || 'pending',  // 催款状态
        collectionCount: parseInt(order.collectionCount) || 0,  // 催款次数
        lastCollectionDate: order.lastCollectionDate ? new Date(order.lastCollectionDate) : null,  // 最后催款日期
        lastCollectionResult: order.lastCollectionResult || '',  // 最后催款结果
        nextFollowupDate: order.nextFollowupDate ? new Date(order.nextFollowupDate) : null,  // 下次跟进日期
        orderNo: order.orderNo || order.id || '',  // 订单编号
        
        // 订单项
        items: items.map(item => ({
          productId: item.itemId,  // 产品ID
          productName: item.itemName,  // 产品名称
          specification: extractSpecification(item.attributes),  // 规格
          quantity: parseInt(item.quantity) || 0,  // 数量
          unit: extractUnit(item.extraMap),  // 单位
          unitPrice: parseFloat(item.skuPrice) || 0,  // 单价
          amount: parseFloat(item.fee) || 0,  // 金额（使用fee字段而不是amount字段）
          remark: item.remark || ''  // 备注
        }))
      };
    });
    
    logger.info(`[PluginData] 成功提取 ${orders.length} 条订单数据`);
    return orders;
  } catch (error) {
    logger.error('[PluginData] 提取订单数据失败:', error);
    return [];
  }
}

// 映射订单状态
function mapOrderStatus(status) {
  // 政采云订单状态映射
  const statusMap = {
    '1': 'pending',      // 待处理
    '3': 'shipped',      // 已发货
    '4': 'completed',    // 已完成
    '-5': 'cancelled',   // 已取消
    '5': 'completed'     // 已验收
  };
  
  return statusMap[status] || 'pending';
}

// 映射开票状态
function mapInvoiceStatus(status) {
  // 政采云开票状态映射
  const statusMap = {
    'uninvoiced': 'uninvoiced',  // 未开票
    'invoiced': 'invoiced',      // 已开票
    'sent': 'sent'               // 已寄送
  };
  
  return statusMap[status] || 'uninvoiced';
}

// 提取规格信息
function extractSpecification(attributes) {
  if (!attributes || !Array.isArray(attributes)) {
    return '';
  }
  
  return attributes.map(attr => `${attr.key}:${attr.value}`).join('; ');
}

// 提取单位信息
function extractUnit(extraMap) {
  if (!extraMap) {
    return '';
  }
  
  return extraMap.unit || '';
}

// 手动处理订单数据
async function manualProcessOrders() {
  try {
    // 连接数据库
    await sequelize.authenticate();
    logger.info('数据库连接成功');
    
    // 读取政采云销售订单数据文件
    const rawData = fs.readFileSync(path.join(__dirname, '通过插件拦截响应数据（政采云销售订单）.md'), 'utf8');
    const jsonData = JSON.parse(rawData);
    
    // 获取数据项
    const dataItems = jsonData.data;
    logger.info(`总共有 ${dataItems.length} 个数据项`);
    
    // 处理每个数据项
    for (let i = 0; i < dataItems.length; i++) {
      const item = dataItems[i];
      logger.info(`处理第 ${i+1} 个数据项: ${item.url}`);
      
      // 提取订单数据
      const orders = extractOrdersFromPluginData(item);
      logger.info(`从第 ${i+1} 个数据项中提取到 ${orders.length} 个订单`);
      
      // 保存订单数据到数据库
      for (let j = 0; j < orders.length; j++) {
        const order = orders[j];
        logger.info(`  保存订单 ${j+1}: ID=${order.platformOrderId}, 金额=${order.orderAmount}`);
        
        try {
          // 检查订单是否已存在
          const existingOrder = await SalesOrderModel.findOne({
            where: {
              platformOrderId: order.platformOrderId
            }
          });
          
          if (existingOrder) {
            logger.info(`    订单已存在，跳过: ${order.platformOrderId}`);
            continue;
          }
          
          // 保存订单主表
          const savedOrder = await SalesOrderModel.create({
            organizationId: 1, // 默认组织ID
            platformOrderId: order.platformOrderId,
            customerId: order.customerId,
            orderAmount: order.orderAmount,
            orderStatus: order.orderStatus,
            orderDate: order.orderDate,
            platformSource: order.platformSource,
            invoiceStatus: order.invoiceStatus,
            invoiceDate: order.invoiceDate,
            responsibleSalesman: order.responsibleSalesman,
            isGift: order.isGift,
            collectionStatus: order.collectionStatus,
            collectionCount: order.collectionCount,
            lastCollectionDate: order.lastCollectionDate,
            lastCollectionResult: order.lastCollectionResult,
            nextFollowupDate: order.nextFollowupDate,
            orderNo: order.orderNo
          });
          
          logger.info(`    订单保存成功，ID: ${savedOrder.id}`);
          
          // 保存订单详情项
          if (order.items && order.items.length > 0) {
            const orderItems = order.items.map(item => ({
              orderId: savedOrder.id,
              productId: item.productId,
              productName: item.productName,
              specification: item.specification,
              quantity: item.quantity,
              unit: item.unit,
              unitPrice: item.unitPrice,
              amount: item.amount,
              remark: item.remark
            }));
            
            const savedItems = await SalesOrderItemModel.bulkCreate(orderItems);
            logger.info(`    保存了 ${savedItems.length} 个订单项`);
          }
        } catch (saveError) {
          logger.error(`    保存订单失败: ${order.platformOrderId}`, saveError);
        }
      }
    }
    
    // 检查最终结果
    const orderCount = await SalesOrderModel.count();
    const itemCount = await SalesOrderItemModel.count();
    logger.info(`处理完成。当前销售订单表中有 ${orderCount} 条记录，订单项表中有 ${itemCount} 条记录`);
  } catch (error) {
    logger.error('处理过程中发生错误:', error);
  } finally {
    await sequelize.close();
  }
}

// 运行手动处理
manualProcessOrders();